from pyppeteer.page import Page
from lxml import etree
from threading import Thread
from config import *
from tool import *
from api import *
from sql import sqlseverDB
import requests,concurrent.futures
import asyncio,string,inspect

class AdPro:
    def __init__(self,basic_info):
        self.basic_info=basic_info
        self.basic_info['host']='seller.shopee.co.id'
        self.basic_info['is_KJ']=0
        self.bid=basic_info['BrowserID']
        self.dpName=basic_info['DpName']
        self.userName = basic_info['UserName']
        self.password = basic_info['Password']
        self.gname=basic_info['GroupName']
        self.session=requests.session()
        self.ck=None
        self.SPC_CDS=None
        self.itemid=None
        self.xc_status=False
        self.ppp=555
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'sumaitongpol', SQL_USER, SQL_PWD)

    async def close_browser(self,bro):
        bid=self.bid
        bname=self.dpName
        task_id=self.task_id
        aff_close=tms.ExecNoQuery('update taskstatus set is_on=0 where bid=? and task_id=?',(bid,task_id))
        rs= tms.ExecQuery('select count(*) from taskstatus where bid=? and task_id!=? and is_on=1',(bid,task_id))
        
        if rs[0][0]==0:
            try:
                await bro.close()
                logging.info(f'店铺:{bname},{bid},关闭浏览器成功,记录状态:{aff_close}')
            except Exception as e:
                logging.error(f'店铺:{bname},{bid},关闭浏览器失败,记录状态:{aff_close}')

        else:
            logging.info(f'店铺:{bname},{bid},有其他 {rs[0][0]} 个任务,正在运行,稍后关闭')

    async def check_login(self,page:Page):

        wait_count=0

        while True:
            if MAX_WAIT and wait_count>MAX_WAIT:
                print(f'店铺:{self.dpName},等待时间过长,登录失败')
                return
            try:
                acc=await page.xpath('//div[@class="subaccount-info"]')
                no_login=await page.xpath('//input[@name="loginKey"]')
                if no_login:
                    print(f'店铺:{self.dpName},账号未登录,进行登录')
                    await repeat_reload(page)
                    cot=await page.content()
                    el_isusername=etree.HTML(cot).xpath('//input[@name="loginKey"]/@value')
                    haspad=False
                    if el_isusername:
                        cusername=el_isusername[0]
                        if cusername:
                            haspad=True
                    if not haspad:
                        await page.type('input[name=loginKey]',self.userName,{'delay': 120})
                        await page.type('input[name=password]',self.password,{'delay': 120})
                    await page.waitFor(1000)
                    btn_login=(await page.xpath('//form/button[1]'))[0]
                    await btn_login.click()
                    await asyncio.sleep(15)
                    
                if acc:
                    print(f'店铺:《{self.dpName}》,已登录')
                    return page
                wait_count+=1
                    
            except Exception as e:
                print(e)
                wait_count+=1
            await asyncio.sleep(2)

    async def get_ck(self):

        task_id=self.task_id
        browser=await openSunBrowser(self.basic_info,task_id)
        page=await browser.newPage()
        await repeat_goto(page,'https://seller.shopee.co.id/portal/marketing/pas/product/create?source_page_id=1')            
        await self.check_login(page)
        if not page:
            await self.close_browser(browser)
            return
        await asyncio.sleep(5)

        res_ad=await js_get_pro_trait_list(page,self.basic_info,is_only_one=True)
        if res_ad is None:
            await page.close()
            await self.close_browser(browser)
            return
        p_no_ad,p_has_ad=res_ad
        if len(p_no_ad)+len(p_has_ad)==0:
            await page.close()
            await self.close_browser(browser)
            return
        all_entry=p_no_ad+p_has_ad
        random_item = random.choice(all_entry)
        pid=random_item['item_id']
        self.itemid=pid
        cookies = await page.cookies()
        cksstrlist=[]
        for cookie in cookies:
            cksstrlist.append(f"{cookie['name']}={cookie['value']}")
            if cookie['name']=='SPC_CDS':
                SPC_CDS=cookie['value']
        self.ck=';'.join(cksstrlist)
        self.SPC_CDS=SPC_CDS
        print(f'店铺:《{self.dpName}》,获取cookie成功,同时获取店铺ID:{self.SPC_CDS}')

        await page.close()
        await self.close_browser(browser)
        return self.ck,SPC_CDS,pid

    def req_list_word(self,kitem,ck_info,kinfo=None):
        ck,spdc,pid=ck_info
        kid,kw=kitem
        url='https://seller.shopee.co.id/api/pas/v1/setup_helper/list_keyword_hint/'
        headers={
            'cookie':ck,
            #'sc-fe-session': '123A0BFF2F365AEA',
            'user-agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/125.0.0.0 Safari/537.36'
        }

        params={
            'SPC_CDS':spdc,
            'SPC_CDS_VER': 2
        }

        data={
            'item_id': pid, 
            'keyword': kw,
            'placement': 0,
            'suggest_log_data': {'page': 'suggest_creation'}
        }

        try:
            res=self.session.post(url,params=params,json=data,headers=headers)
            if res.status_code==200:
                resjs=res.json()
                if resjs['msg']=='OK':
                    listdata=resjs['data']
                    #print(f'kid:{kid},《{kw}》,下拉词共采集{len(listdata)}个')
                    return {'code':1,'kid':kid,'kw':f'《{kw}》','msg':f'查询成功','data':listdata,'kinfo':kinfo}
                else:
                    return {'code':-2,'kid':kid,'kw':f'《{kw}》','msg':f'查询错误=>{resjs}'}
            else:
                return {'code':-3,'kid':kid,'kw':f'《{kw}》','msg':f'网络错误=>{res.status_code}'}

        except Exception as e:
            return {'code':-4,'kid':kid,'kw':f'《{kw}》','msg':f'查询失败 => {e}'}

    def cj_list_keyword(self):
        '''ADTitKW表1~6词采集'''
        lm_list=self.lm_list
        task_id=self.task_id
        if len(lm_list)==0:
            close_SunBrowser(self.bid,task_id)
            return {'code':0,'msg':'无词待采集下拉'}
        
        
        res_ck=asyncio.run(self.get_ck())
        if not res_ck:
            close_SunBrowser(self.bid,task_id)
            return {'code':-2,'msg':'cookie获取失败'}

        print(f'表《ADTitKw》开始采集下拉词, :{len(lm_list)}')

        kj=1
        err_count=0
        for r in lm_list:
            
            qj_count=0
            kid=r['id']
            xl_count=0
            kw=r['KW']
            prodcut_id=r['ProductID']
            ptit=r['PTitle']
            yskws=json.loads(r['KWList'])
            wnum=r['WordNum']
            aff=0
            for letter in list(string.ascii_lowercase)+['0','1','2','3','4','5','6','7','8','9']:

                res=self.req_list_word([kid,f'{kw} {letter}'],res_ck)
                if res['code']==1:
                    listdata=res['data']
                    xl_count+=len(listdata)
                    
                    for item in listdata:
                        is_recommended,cur_kw=item['is_recommended'],item['keyword']
                        cur_fgkw=cur_kw.split()
                        cur_kw_num=len(cur_fgkw)
                        if cur_kw_num<=wnum:
                            #print(f'kid:{kid},kw:{kw},采集的:《{cur_kw}》,词数小于等于目标采集词单词数,跳过保存')
                            continue
                        flag=True
                        for fgkw in [kkkitem.lower() for kkkitem in cur_fgkw]:
                            if fgkw not in yskws:
                                flag =False
                                break

                        if not flag:
                            #print(f'kid:{kid},kw:{kw},采集的:《{cur_kw}》,不完全被标题包含跳过')
                            continue

                        rs=self.ms.ExecQuery('select count(*) from ADTitKW where kw=? and ProductID=?',(cur_kw,prodcut_id))

                        if rs[0][0]>0:
                            #print(f'kid:{kid},kw:{kw},采集的:《{cur_kw}》,数据库已存在')
                            pass
                        else:
                            res_kinfo=copy.copy(r)
                            if cur_kw_num<=10:
                                if res_kinfo.get('id'):
                                    res_kinfo.pop('id')
                                res_kinfo.pop('SearchCount')
                                res_kinfo['KW']=cur_kw
                                res_kinfo['WordNum']=cur_kw_num
                                res_kinfo['recommended']=is_recommended
                                res_kinfo['exposure']=1
                                zdstr=','.join(res_kinfo.keys())
                                prarms=list(res_kinfo.values())
                                wstr=','.join(['?' for _ in prarms])
                                aff+=self.ms.ExecNoQuery(f'insert into ADTitKW({zdstr}) values({wstr})',prarms)
                            
                    qj_count+=1
                else:
                    print(res)
                    res_ck=asyncio.run(self.get_ck())
                    if not res_ck:
                        close_SunBrowser(self.bid,task_id)
                        return {'code':-2,'msg':'cookie获取失败'}
                    err_count+=1
            if err_count>30:
                break
            aff2=0
            if qj_count>0:
                aff2=self.ms.ExecNoQuery('update ADTitKW set state=1 where id=? and state=0',(kid,))

            logging.info(f'店铺:《{self.dpName}》,{self.bid},第{kj}个词:{kw},共请求{qj_count}次,采集{xl_count}个下拉词,去重后保存{aff}个,更改采集状态:{aff2}')   
            kj+=1
        return {'code':0,'msg':f'采集下拉词成功','aff':qj_count}

    def cj_click_keyword(self):
        '''点击任务表采集下拉词'''
        lm_list=self.lm_list
        task_id=self.task_id
        if len(lm_list)==0:
            close_SunBrowser(self.bid,task_id)
            return {'code':0,'msg':'无词待采集下拉'}
        
        
        res_ck=asyncio.run(self.get_ck())
        if not res_ck:
            close_SunBrowser(self.bid,task_id)
            return {'code':-2,'msg':'cookie获取失败'}

        print(f'开始采集点击任务表下拉词:{len(lm_list)}个')

        kj=1
        err_count=0
        for r in lm_list:
            qj_count=0
            kid=r['id']
            xl_count=0
            kw=r['KW']
            prodcut_id=r['itemid']
            yskws=json.loads(r['KWList'])
            wnum=r['WordNum']
            from_click_task_id=r['from_click_task_id']
            aff=0
            for letter in list(string.ascii_lowercase)+['0','1','2','3','4','5','6','7','8','9']:

                res=self.req_list_word([kid,f'{kw} {letter}'],res_ck)
                if res['code']==1:
                    listdata=res['data']
                    xl_count+=len(listdata)
                    
                    for item in listdata:
                        cur_kw=item['keyword']
                        cur_fgkw=cur_kw.split()
                        cur_kw_num=len(cur_fgkw)
                        if cur_kw_num<=wnum:
                            #print(f'kid:{kid},kw:{kw},采集的:《{cur_kw}》,词数小于等于目标采集词单词数,跳过保存')
                            continue
                        flag=True
                        for fgkw in [kkkitem.lower() for kkkitem in cur_fgkw]:
                            if fgkw not in yskws:
                                flag =False
                                break

                        if not flag:
                            #print(f'kid:{kid},kw:{kw},采集的:《{cur_kw}》,不完全被标题包含跳过')
                            continue

                        rs=self.ms.ExecQuery('select count(*) from Click_Task_KW where kw=? and itemid=?',(cur_kw,prodcut_id))

                        if rs[0][0]>0:
                            #print(f'kid:{kid},kw:{kw},采集的:《{cur_kw}》,数据库已存在')
                            pass
                        else:
                            kdata={
                                'itemid':prodcut_id,
                                'campaign_id':r['campaign_id'],
                                'pname':r['pname'],
                                'KW':cur_kw,
                                'WordNum':cur_kw_num,
                                'KWList':r['KWList'],
                                'from_click_task_id':from_click_task_id
                            }
                            czds=list(kdata.keys())
                            cparas=list(kdata.values())
                            aff+=self.ms.merge_into('Click_Task_KW',czds,cparas,['itemid','KW'],True)
                            
                    qj_count+=1
                else:
                    print(res)
                    res_ck=asyncio.run(self.get_ck())
                    if not res_ck:
                        close_SunBrowser(self.bid,task_id)
                        return {'code':-2,'msg':'cookie获取失败'}
                    err_count+=1
            if err_count>30:
                break
            aff2=0
            if qj_count>0:
                aff2=self.ms.ExecNoQuery('update Click_Task_KW set is_cj_xl=1 where id=? and is_cj_xl=0',(kid,))

            logging.info(f'店铺:《{self.dpName}》,{self.bid},第{kj}个词:{kw},共请求{qj_count}次,采集{xl_count}个下拉词,去重后保存{aff}个,更改采集状态:{aff2}')   
            kj+=1
        return {'code':0,'msg':f'采集下拉词成功','aff':qj_count}
      
    async def cj_Search_Volume(self):
        '''YNADCSV表搜索量'''

        lm_list=self.lm_list
        if not lm_list:
            return {'code':0,'msg':'无待采集列表'}
        browser = await openSunBrowser(self.basic_info,self.task_id)
        #browser=await openChrome()
        
        if not browser:
            return {'code':-1,'msg':'启动浏览器失败'}
        page=await browser.newPage()
        url=f'https://seller.shopee.co.id/portal/marketing/pas/product/create?source_page_id=1'
        await repeat_goto(page,url)
        sss=await self.check_login(page)
        if not sss:
            await self.close_browser(browser)
            return {'code':-2,'msg':'账户登录失败'}
        await asyncio.sleep(5)
        res_ad=await js_get_pro_trait_list(page,self.basic_info,is_only_one=True)
        if res_ad is None:
            await page.close()
            await self.close_browser(browser)
            return {'code':0,'msg':'获取广告产品列表失败'}
        p_no_ad,p_has_ad=res_ad
        if len(p_no_ad)+len(p_has_ad)==0:
            await page.close()
            await self.close_browser(browser)
            return {'code':0,'msg':'店铺无产品'}
        all_entry=p_no_ad+p_has_ad
        random_item = random.choice(all_entry)
        pid=random_item['item_id']

        task_count=len(lm_list)
        kj=1
        suc=0
        for kid,kw,tb_name in lm_list:
            res_sss=await js_req_search_count(page,kw,pid)
            if res_sss is not None:
                aff=self.ms.ExecNoQuery(f'update {tb_name} set Search_Volume=? where [Search_Query]=? and [id]=?',(res_sss,kw,kid))
                suc+=aff
                
            else:
                await repeat_goto(url)
                sss=await self.check_login(page)
                if not sss:
                    await self.close_browser(browser)
                    return {'code':-2,'msg':'账户登录失败'}
       
            task_count-=1
            kj+=1

        logging.info(f'店铺:《{self.dpName}》,{self.bid},itemid:{pid},共 {len(lm_list)} 个词,成功采集 {suc} 个')
        await page.close()
        await self.close_browser(browser)
        return {'code':0,'msg':f'采集{suc}搜索量成功','aff':suc}
            
    async def cj_AD_KW_searchcount(self):
        '''ADTitKW表搜索量'''
        lm_list=self.lm_list
        if not lm_list:
            return {'code':0,'msg':'无待采集列表'}
        browser = await openSunBrowser(self.basic_info,self.task_id)
        #browser=await openChrome()
        
        if not browser:
            return {'code':-1,'msg':'启动浏览器失败'}
        page=await browser.newPage()
        url=f'https://seller.shopee.co.id/portal/marketing/pas/product/create?source_page_id=1'
        await repeat_goto(page,url)
        sss=await self.check_login(page)
        if not sss:
            await self.close_browser(browser)
            return {'code':-2,'msg':'账户登录失败'}
        await asyncio.sleep(5)
        res_ad=await js_get_pro_trait_list(page,self.basic_info,is_only_one=True)
        if res_ad is None:
            await page.close()
            await self.close_browser(browser)
            return {'code':0,'msg':'获取广告产品列表失败'}
        p_no_ad,p_has_ad=res_ad
        if len(p_no_ad)+len(p_has_ad)==0:
            await page.close()
            await self.close_browser(browser)
            return {'code':0,'msg':'店铺无产品'}
        all_entry=p_no_ad+p_has_ad
        random_item = random.choice(all_entry)
        pid=random_item['item_id']

        task_count=len(lm_list)
        kj=1
        suc=0
        for kid,kw in lm_list:
            res_sss=await js_req_search_count(page,kw,pid)
            if res_sss is not None:
                aff=self.ms.ExecNoQuery('update [ADTitKW] set SearchCount=? where [KW]=? and [id]=?',(res_sss,kw,kid))
                suc+=aff
                logging.info(f'店铺:《{self.dpName}》,{self.bid},第{kj}个词保存搜索量:{res_sss},状态:{aff}')
                
            else:
                await repeat_goto(url)
                sss=await self.check_login(page)
                if not sss:
                    await self.close_browser(browser)
                    return {'code':-2,'msg':'账户登录失败'}
            
            task_count-=1
            kj+=1

        logging.info(f'店铺:《{self.dpName}》,{self.bid},itemid:{pid},共 {len(lm_list)} 个词,成功采集 {suc} 个')
        await page.close()
        await self.close_browser(browser)
        return {'code':0,'msg':f'采集{suc}搜索量成功','aff':suc}

    async def cj_click_search(self):
        '''点击任务表采集搜索量'''
        lm_list=self.lm_list
        if not lm_list:
            return {'code':0,'msg':'无待采集列表'}
        browser = await openSunBrowser(self.basic_info,self.task_id)
        #browser=await openChrome()
        
        if not browser:
            return {'code':-1,'msg':'启动浏览器失败'}
        page=await browser.newPage()
        url=f'https://seller.shopee.co.id/portal/marketing/pas/product/create?source_page_id=1'
        await repeat_goto(page,url)
        sss=await self.check_login(page)
        if not sss:
            await self.close_browser(browser)
            return {'code':-2,'msg':'账户登录失败'}
        await asyncio.sleep(5)
        res_ad=await js_get_pro_trait_list(page,self.basic_info,is_only_one=True)
        if res_ad is None:
            await page.close()
            await self.close_browser(browser)
            return {'code':0,'msg':'获取广告产品列表失败'}
        p_no_ad,p_has_ad=res_ad
        if len(p_no_ad)+len(p_has_ad)==0:
            await page.close()
            await self.close_browser(browser)
            return {'code':0,'msg':'店铺无产品'}
        all_entry=p_no_ad+p_has_ad
        random_item = random.choice(all_entry)
        pid=random_item['item_id']

        task_count=len(lm_list)
        kj=1
        suc=0
        for kid,kw in lm_list:
            res_sss=await js_req_search_count(page,kw,pid)
            if res_sss is not None:
                aff=self.ms.ExecNoQuery('update Click_Task_KW set SearchCount=? where [KW]=? and [id]=?',(res_sss,kw,kid))
                suc+=aff
                logging.info(f'店铺:《{self.dpName}》,{self.bid},第{kj}个词保存搜索量:{res_sss},状态:{aff}')
                
            else:
                await repeat_goto(url)
                sss=await self.check_login(page)
                if not sss:
                    await self.close_browser(browser)
                    return {'code':-2,'msg':'账户登录失败'}
            
            task_count-=1
            kj+=1

        logging.info(f'店铺:《{self.dpName}》,{self.bid},itemid:{pid},共 {len(lm_list)} 个词,成功采集 {suc} 个')
        await page.close()
        await self.close_browser(browser)
        return {'code':0,'msg':f'采集{suc}搜索量成功','aff':suc}
    

    def update_taskStatus(self,tstate,err_msg=None,is_cl=1):

        end_zds=['bid','task_id','task_name','bname','gname','state','err_msg']
        end_params=[self.bid,self.task_id,self.rw_name,self.dpName,self.gname,tstate,err_msg]
        if is_cl==0:
            end_zds.append('is_cl')
            end_params.append(is_cl)
        err_str=f'错误信息:{err_msg},' if err_msg else ''
        aff_rw=tms.merge_into('taskStatus',end_zds,end_params,['bid','task_id'],True)
        logging.info(f'店铺:《{self.dpName}》,{self.bid} 任务:《{self.rw_name}》,{err_str}标记状态:{aff_rw}')
        

    def start_task(self,task_id):
        rw_name=Task_Name_dict[task_id]
        fun_name=Task_Fuc_dict[task_id]
        self.task_id=task_id
        self.rw_name=rw_name

        try:             
            func = getattr(self, fun_name)
            if inspect.iscoroutinefunction(func):
                res=asyncio.run(func())
            else:
                res=func()

            code=res['code']
            msg=res['msg']
            if code==0:
                self.update_taskStatus(1)
            else:
                self.update_taskStatus(0,err_msg=msg,is_cl=0)
            return res
        
        except Exception as e:
            close_SunBrowser(self.bid,task_id)
            traceback.print_exc()
            self.update_taskStatus(0,err_msg=str(e),is_cl=0)